<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Data Consistency Checks at the Application Level</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.1.2 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Concurrency Control"
HREF="mvcc.html"><LINK
REL="PREVIOUS"
TITLE="Explicit Locking"
HREF="explicit-locking.html"><LINK
REL="NEXT"
TITLE="Locking and Indexes"
HREF="locking-indexes.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2011-12-01T22:07:59"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.1.2 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="Explicit Locking"
HREF="explicit-locking.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="mvcc.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 13. Concurrency Control</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="Locking and Indexes"
HREF="locking-indexes.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="APPLEVEL-CONSISTENCY"
>13.4. Data Consistency Checks at the Application Level</A
></H1
><P
>    It is very difficult to enforce business rules regarding data integrity
    using Read Committed transactions because the view of the data is
    shifting with each statement, and even a single statement may not
    restrict itself to the statement's snapshot if a write conflict occurs.
   </P
><P
>    While a Repeatable Read transaction has a stable view of the data
    throughout its execution, there is a subtle issue with using
    <ACRONYM
CLASS="ACRONYM"
>MVCC</ACRONYM
> snapshots for data consistency checks, involving
    something known as <I
CLASS="FIRSTTERM"
>read/write conflicts</I
>.
    If one transaction writes data and a concurrent transaction attempts
    to read the same data (whether before or after the write), it cannot
    see the work of the other transaction.  The reader then appears to have
    executed first regardless of which started first or which committed
    first.  If that is as far as it goes, there is no problem, but
    if the reader also writes data which is read by a concurrent transaction
    there is now a transaction which appears to have run before either of
    the previously mentioned transactions.  If the transaction which appears
    to have executed last actually commits first, it is very easy for a
    cycle to appear in a graph of the order of execution of the transactions.
    When such a cycle appears, integrity checks will not work correctly
    without some help.
   </P
><P
>    As mentioned in <A
HREF="transaction-iso.html#XACT-SERIALIZABLE"
>Section 13.2.3</A
>, Serializable
    transactions are just Repeatable Read transactions which add
    non-blocking monitoring for dangerous patterns of read/write conflicts.
    When a pattern is detected which could cause a cycle in the apparent
    order of execution, one of the transactions involved is rolled back to
    break the cycle.
   </P
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="SERIALIZABLE-CONSISTENCY"
>13.4.1. Enforcing Consistency With Serializable Transactions</A
></H2
><P
>     If the Serializable transaction isolation level is used for all writes
     and for all reads which need a consistent view of the data, no other
     effort is required to ensure consistency.  Software from other
     environments which is written to use serializable transactions to
     ensure consistency should <SPAN
CLASS="QUOTE"
>"just work"</SPAN
> in this regard in
     <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>.
    </P
><P
>     When using this technique, it will avoid creating an unnecessary burden
     for application programmers if the application software goes through a
     framework which automatically retries transactions which are rolled
     back with a serialization failure.  It may be a good idea to set
     <TT
CLASS="LITERAL"
>default_transaction_isolation</TT
> to <TT
CLASS="LITERAL"
>serializable</TT
>.
     It would also be wise to take some action to ensure that no other
     transaction isolation level is used, either inadvertently or to
     subvert integrity checks, through checks of the transaction isolation
     level in triggers.
    </P
><P
>     See <A
HREF="transaction-iso.html#XACT-SERIALIZABLE"
>Section 13.2.3</A
> for performance suggestions.
    </P
><DIV
CLASS="WARNING"
><P
></P
><TABLE
CLASS="WARNING"
BORDER="1"
WIDTH="100%"
><TR
><TD
ALIGN="CENTER"
><B
>Warning</B
></TD
></TR
><TR
><TD
ALIGN="LEFT"
><P
>      This level of integrity protection using Serializable transactions
      does not yet extend to hot standby mode (<A
HREF="hot-standby.html"
>Section 25.5</A
>).
      Because of that, those using hot standby may want to use Repeatable
      Read and explicit locking.on the master.
     </P
></TD
></TR
></TABLE
></DIV
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="NON-SERIALIZABLE-CONSISTENCY"
>13.4.2. Enforcing Consistency With Explicit Blocking Locks</A
></H2
><P
>     When non-serializable writes are possible,
     to ensure the current validity of a row and protect it against
     concurrent updates one must use <TT
CLASS="COMMAND"
>SELECT FOR UPDATE</TT
>,
     <TT
CLASS="COMMAND"
>SELECT FOR SHARE</TT
>, or an appropriate <TT
CLASS="COMMAND"
>LOCK
     TABLE</TT
> statement.  (<TT
CLASS="COMMAND"
>SELECT FOR UPDATE</TT
>
     and <TT
CLASS="COMMAND"
>SELECT FOR SHARE</TT
> lock just the
     returned rows against concurrent updates, while <TT
CLASS="COMMAND"
>LOCK
     TABLE</TT
> locks the whole table.)  This should be taken into
     account when porting applications to
     <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> from other environments.
    </P
><P
>     Also of note to those converting from other environments is the fact
     that <TT
CLASS="COMMAND"
>SELECT FOR UPDATE</TT
> does not ensure that a
     concurrent transaction will not update or delete a selected row.
     To do that in <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> you must actually
     update the row, even if no values need to be changed.
     <TT
CLASS="COMMAND"
>SELECT FOR UPDATE</TT
> <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>temporarily blocks</I
></SPAN
>
     other transactions from acquiring the same lock or executing an
     <TT
CLASS="COMMAND"
>UPDATE</TT
> or <TT
CLASS="COMMAND"
>DELETE</TT
> which would
     affect the locked row, but once the transaction holding this lock
     commits or rolls back, a blocked transaction will proceed with the
     conflicting operation unless an actual <TT
CLASS="COMMAND"
>UPDATE</TT
> of
     the row was performed while the lock was held.
    </P
><P
>     Global validity checks require extra thought under
     non-serializable <ACRONYM
CLASS="ACRONYM"
>MVCC</ACRONYM
>.
     For example, a banking application might wish to check that the sum of
     all credits in one table equals the sum of debits in another table,
     when both tables are being actively updated.  Comparing the results of two
     successive <TT
CLASS="LITERAL"
>SELECT sum(...)</TT
> commands will not work reliably in
     Read Committed mode, since the second query will likely include the results
     of transactions not counted by the first.  Doing the two sums in a
     single repeatable read transaction will give an accurate picture of only the
     effects of transactions that committed before the repeatable read transaction
     started &mdash; but one might legitimately wonder whether the answer is still
     relevant by the time it is delivered.  If the repeatable read transaction
     itself applied some changes before trying to make the consistency check,
     the usefulness of the check becomes even more debatable, since now it
     includes some but not all post-transaction-start changes.  In such cases
     a careful person might wish to lock all tables needed for the check,
     in order to get an indisputable picture of current reality.  A
     <TT
CLASS="LITERAL"
>SHARE</TT
> mode (or higher) lock guarantees that there are no
     uncommitted changes in the locked table, other than those of the current
     transaction.
    </P
><P
>     Note also that if one is relying on explicit locking to prevent concurrent
     changes, one should either use Read Committed mode, or in Repeatable Read
     mode be careful to obtain
     locks before performing queries.  A lock obtained by a
     repeatable read transaction guarantees that no other transactions modifying
     the table are still running, but if the snapshot seen by the
     transaction predates obtaining the lock, it might predate some now-committed
     changes in the table.  A repeatable read transaction's snapshot is actually
     frozen at the start of its first query or data-modification command
     (<TT
CLASS="LITERAL"
>SELECT</TT
>, <TT
CLASS="LITERAL"
>INSERT</TT
>,
     <TT
CLASS="LITERAL"
>UPDATE</TT
>, or <TT
CLASS="LITERAL"
>DELETE</TT
>), so
     it is possible to obtain locks explicitly before the snapshot is
     frozen.
    </P
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="explicit-locking.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="locking-indexes.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Explicit Locking</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="mvcc.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Locking and Indexes</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>